package com.LostAndFound.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.LostAndFound.domain.SystemInfo;
import com.LostAndFound.utils.DataSourceUtils;

public class SystemInfoDao {

	// 分页，得到记录的总页数
	public int getTotalInfoForEasyUI() throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from info";
		Long total = (Long) runner.query(sql, new ScalarHandler());
		return total.intValue();
	}
	// 分页，limit查询，按日期排序
	public List<Map<String, Object>> getInfoListForEasyUI(int startIndex, int pageSize) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select i.infoid,i.title,i.infoDetail,i.datetime,r.username,r.rid "
				+ "from info i,root r where i.rid=r.rid order by datetime desc limit ?,?";
		return runner.query(sql, new MapListHandler(),startIndex,pageSize);
	}

	public void handleDeleteSystemInfoForEasyUI(String infoid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from info where infoid = ?";
		runner.update(sql, infoid);
	}

	public void handleAddSystemInfo(SystemInfo systemInfo) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "insert into info values(?,?,?,?,?)";
		Object[] params = {systemInfo.getInfoid(),systemInfo.getTitle(),systemInfo.getInfoDetail(),
				systemInfo.getDatetime(),systemInfo.getRoot().getRid()}; 
		runner.update(sql, params);
	}

}
